package cn.fengwoo.springBoot.utils;


import org.springframework.jdbc.core.JdbcTemplate;

import java.util.List;
import java.util.Map;

/**
 * Created by fangdaji on 2018/1/18.
 */
public class JdbcPageUtil {

    public static List<Map<String, Object>> queryForList(String sql, JdbcTemplate jTemplate, int currentPage, int numPerPage){
        return queryForList(sql,jTemplate,currentPage,numPerPage);
    }

    /**分页
     * @param sql 包含筛选条件的sql，但不包含分页相关约束，如mysql的limit
     * @param jTemplate JdbcTemplate实例
     * @param currentPage 当前页
     * @param numPerPage 每页记录数
     * @param args 参数
     */
    public static List<Map<String, Object>> queryForList(String sql, JdbcTemplate jTemplate, int currentPage, int numPerPage, Object[] args) {
        if(jTemplate == null){
            throw new IllegalArgumentException("Page.jTemplate is null");
        }else if(sql == null || sql.equals("")){
            throw new IllegalArgumentException("Page.sql is empty");
        }

        //计算总记录数
        StringBuffer totalSQL = new StringBuffer(" SELECT count(*) FROM ( ");
        totalSQL.append(sql);
        totalSQL.append(" ) totalTable ");

        Map<String, Object> map = null;
        if(args!=null){
            map = jTemplate.queryForMap(totalSQL.toString(),args);
        }else{
            map = jTemplate.queryForMap(totalSQL.toString());
        }

        //总记录数
        int totalRows = ((Long) map.get("count(*)")).intValue();

        //计算总页数
        int totalPages = setTotalPages(totalRows, numPerPage);

        //计算起始行数
        int startIndex = setStartIndex(currentPage, numPerPage);

        //计算结束行数
        int lastIndex = setLastIndex(totalRows, totalPages, currentPage, numPerPage);

        //使用mysql时直接使用limits
        StringBuffer paginationSQL = new StringBuffer();
        paginationSQL.append(sql);
        paginationSQL.append(" limit " + startIndex + "," + lastIndex);

        //装入结果集

        List<Map<String, Object>> mapList = null;
        if(args!=null){
            mapList = jTemplate.queryForList(paginationSQL.toString(),args);
        }else{
            mapList = jTemplate.queryForList(paginationSQL.toString());
        }

        return mapList;
    }

    //计算总页数
    public static int setTotalPages(int totalRows,int numPerPage) {
        if(totalRows % numPerPage == 0){
            return totalRows / numPerPage;
        }else{
            return (totalRows / numPerPage) + 1;
        }
    }

    public static int setStartIndex(int currentPage,int numPerPage) {
        return  (currentPage - 1) * numPerPage;
    }

    //计算结束时候的索引
    public static int setLastIndex(int totalRows,int totalPages,int currentPage,int numPerPage) {
        if( totalRows < numPerPage){
            return totalRows;
        }else if((totalRows % numPerPage == 0) || (totalRows % numPerPage != 0 && currentPage < totalPages)){
            return currentPage * numPerPage;
        }else if(totalRows % numPerPage != 0 && currentPage == totalPages){//最后一页
            return totalRows ;
        }
        return 0;
    }
}